﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace LdExpress
{
    public class Schedules
    {
        public long ID = -1;
        public string Mon = "";
        public string Tue = "";
        public string Web = "";
        public string Thu = "";
        public string Fri = "";
        public string Sat = "";
        public string Sun = "";
        public string TS1 = "";
        public string TS2 = "";
        public string CY = "";
        public string CLS = "";
        public string Service = "";
        public string Import = "";
        public string Description = "";
        public long CarrID = -1;
        public long PortToID = -1;
        public long PortFromID = -1;
        public string CarrName = "";//for update
        public string PortToName = "";//for update
        public string PortFromName = "";//for update
        public int Sort = 0;
        public string Remark = "";
        public string Region = "";
        public DateTime LastUpdate = DateTime.Now;

        public Schedules()
        {
        }

        public Schedules(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_schedules";

            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "Insert into " + strTableName + " (" +
                             "`Mon`" +
                             ",`Tue`" +
                             ",`Web`" +
                             ",`Thu`" +
                             ",`Fri`" +
                             ",`Sat`" +
                             ",`Sun`" +
                             ",`TS1`" +
                             ",`TS2`" +
                             ",`CY`" +
                             ",`CLS`" +
                             ",`Service`" +
                             ",`Import`" +
                             ",`Description`" +
                             ",`CarrID`" +
                             ",`PortToID`" +
                             ",`PortFromID`" +
                             ",`Sort`" +
                             ",`Remark`" +
                             ",`Region`" +
                             ",`LastUpdate`" +
                             ") " +
                         " values(" +
                             "'" + DBModule.RefineString(Mon) + "'" +
                             "," + "'" + DBModule.RefineString(Tue) + "'" +
                             "," + "'" + DBModule.RefineString(Web) + "'" +
                             "," + "'" + DBModule.RefineString(Thu) + "'" +
                             "," + "'" + DBModule.RefineString(Fri) + "'" +
                             "," + "'" + DBModule.RefineString(Sat) + "'" +
                             "," + "'" + DBModule.RefineString(Sun) + "'" +
                             "," + "'" + DBModule.RefineString(TS1) + "'" +
                             "," + "'" + DBModule.RefineString(TS2) + "'" +
                             "," + "'" + DBModule.RefineString(CY) + "'" +
                             "," + "'" + DBModule.RefineString(CLS) + "'" +
                             "," + "'" + DBModule.RefineString(Service) + "'" +
                             "," + "'" + DBModule.RefineString(Import) + "'" +
                             "," + "'" + DBModule.RefineString(Description) + "'" +
                             "," + CarrID.ToString() +
                             "," + PortToID.ToString() +
                             "," + PortFromID.ToString() +
                             "," + Sort.ToString() +
                             "," + "'" + DBModule.RefineString(Remark) + "'" +
                             "," + "'" + DBModule.RefineString(Region) + "'" +
                             "," + DBModule.RefineDatetime(LastUpdate) +
                             ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("schedules", "add", cn, trans);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                            "Mon='" + DBModule.RefineString(Mon) + "'" +
                             ",Tue='" + DBModule.RefineString(Tue) + "'" +
                             ",Web='" + DBModule.RefineString(Web) + "'" +
                             ",Thu='" + DBModule.RefineString(Thu) + "'" +
                             ",Fri='" + DBModule.RefineString(Fri) + "'" +
                             ",Sat='" + DBModule.RefineString(Sat) + "'" +
                             ",Sun='" + DBModule.RefineString(Sun) + "'" +
                             ",TS1='" + DBModule.RefineString(TS1) + "'" +
                             ",TS2='" + DBModule.RefineString(TS2) + "'" +
                             ",CY='" + DBModule.RefineString(CY) + "'" +
                             ",CLS='" + DBModule.RefineString(CLS) + "'" +
                             ",Service='" + DBModule.RefineString(Service) + "'" +
                             ",Import='" + DBModule.RefineString(Import) + "'" +
                             ",Description='" + DBModule.RefineString(Description) + "'" +
                             ",CarrID = " + CarrID.ToString() +
                             ",PortToID = " + PortToID.ToString() +
                             ",PortFromID = " + PortFromID.ToString() +
                             ",Sort = " + Sort.ToString() +
                             ",Remark='" + DBModule.RefineString(Remark) + "'" +
                             ",Region='" + DBModule.RefineString(Region) + "'" +
                             ",LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("schedules", "edit", cn, trans);
            }

        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "DELETE FROM tbl_schedules WHERE ID = " + this.ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("schedules", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_schedules WHERE ID = " + ID.ToString() + " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Mon")) Mon = dr["Mon"].ToString();
                if (!dr.IsNull("Tue")) Tue = dr["Tue"].ToString();
                if (!dr.IsNull("Web")) Web = dr["Web"].ToString();
                if (!dr.IsNull("Thu")) Thu = dr["Thu"].ToString();
                if (!dr.IsNull("Fri")) Fri = dr["Fri"].ToString();
                if (!dr.IsNull("Sat")) Sat = dr["Sat"].ToString();
                if (!dr.IsNull("Sun")) Sun = dr["Sun"].ToString();
                if (!dr.IsNull("TS1")) TS1 = dr["TS1"].ToString();
                if (!dr.IsNull("TS2")) TS2 = dr["TS2"].ToString();
                if (!dr.IsNull("CY")) CY = dr["CY"].ToString();
                if (!dr.IsNull("CLS")) CLS = dr["CLS"].ToString();
                if (!dr.IsNull("Service")) Service = dr["Service"].ToString();
                if (!dr.IsNull("Import")) Import = dr["Import"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("CarrID")) CarrID = long.Parse(dr["CarrID"].ToString());
                if (!dr.IsNull("PortToID")) PortToID = long.Parse(dr["PortToID"].ToString());
                if (!dr.IsNull("PortFromID")) PortFromID = long.Parse(dr["PortFromID"].ToString());
                if (!dr.IsNull("Sort")) Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("Remark")) Remark = dr["Remark"].ToString();
                if (!dr.IsNull("Region")) Region = dr["Region"].ToString();
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
            }
        }
        public void Load(long carrID, long polID, long podID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_schedules WHERE CarrID = " + carrID.ToString() +
                " AND PortToID=" + polID + " AND PortFromID=" + podID +
                " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Mon")) Mon = dr["Mon"].ToString();
                if (!dr.IsNull("Tue")) Tue = dr["Tue"].ToString();
                if (!dr.IsNull("Web")) Web = dr["Web"].ToString();
                if (!dr.IsNull("Thu")) Thu = dr["Thu"].ToString();
                if (!dr.IsNull("Fri")) Fri = dr["Fri"].ToString();
                if (!dr.IsNull("Sat")) Sat = dr["Sat"].ToString();
                if (!dr.IsNull("Sun")) Sun = dr["Sun"].ToString();
                if (!dr.IsNull("TS1")) TS1 = dr["TS1"].ToString();
                if (!dr.IsNull("TS2")) TS2 = dr["TS2"].ToString();
                if (!dr.IsNull("CY")) CY = dr["CY"].ToString();
                if (!dr.IsNull("CLS")) CLS = dr["CLS"].ToString();
                if (!dr.IsNull("Service")) Service = dr["Service"].ToString();
                if (!dr.IsNull("Import")) Import = dr["Import"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("CarrID")) CarrID = long.Parse(dr["CarrID"].ToString());
                if (!dr.IsNull("PortToID")) PortToID = long.Parse(dr["PortToID"].ToString());
                if (!dr.IsNull("PortFromID")) PortFromID = long.Parse(dr["PortFromID"].ToString());
                if (!dr.IsNull("Sort")) Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("Remark")) Remark = dr["Remark"].ToString();
                if (!dr.IsNull("Region")) Region = dr["Region"].ToString();
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
            }
        }
        public static void GetList(string strIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_schedules where 1=1 ";
            if ((strIDs != "") && (strIDs != null))
            {
                if (strIDs != "All")
                    strSQL += strIDs;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static void GetListJointCarr(string strCodition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "SELECT tbl_schedules.* ,tbl_carrs.Name,POL.Name as PolName" +
                ",POD.Name as PodName,POD.Country as Country,POD.Region as PodRegion" +
                " FROM tbl_schedules INNER JOIN tbl_carrs ON  tbl_schedules.CarrID =tbl_carrs.ID" +
                " INNER JOIN tbl_ports POL ON tbl_schedules.PortToID=POL.ID " +
            " INNER JOIN tbl_ports POD ON tbl_schedules.PortFromID=POD.ID  where 1=1 ";

            if (strCodition != null && strCodition != "")
            {
                strSQL += strCodition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            else
                strSQL += " ORDER BY POD";
            //System.Web.HttpContext.Current.Response.Write("sql=" + strSQL);
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public void DoSearch(string Condition, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_schedules where 1=1 ";
            if (Condition.Length > 0)
                strSQL += Condition;
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement            
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
    }
}